import pandas as pd
import mysql.connector
from sqlalchemy import create_engine


# 连接到mysql 数据库
def create_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            password=user_password,
            database=db_name
        )
        print("连接数据库成功")
    except mysql.connector.Error as error:
        print("连接数据库失败，错误信息：", error)
        return None

    return connection

# 读取文件
def read_excel_to_set(file_path):
    """ 读取文本文件并将其内容存储到一个集合中 """
    data_set = set()
    try:
        df = pd.read_excel(file_path)
        print(df)
        print("成功读取文本文件并存储到集合中")
        return data_set
    except FileNotFoundError:
        print(f"文件未找到: {file_path}")
        return set()
    except Exception as e:
        print(f"读取文件时出错: {e}")
        return set()

# 存入数据库
def read_set_and_store_in_db(data_set, cursor):
    """ 遍历集合并将内容逐条插入到数据库中 """
    for id_value, name_value in data_set:
        try:
            cursor.execute('INSERT INTO douyin_collect (userId, userName) VALUES (%s, %s)', (id_value, name_value))
        except mysql.connector.Error as e:
            print(f"插入数据时出错: {id_value} - {name_value} - {e}")



# 主程序
if __name__ == '__main__':
    # 数据库连接信息
    host_name = "localhost"
    user_name = "root"
    user_password = "Dream2060#*@*#"
    db_name = "pwxk"

    # 创建数据库连接
    db_connection = create_connection(host_name, user_name, user_password, db_name)

    cursor = db_connection.cursor()

    # 文件路径
    folder_path = 'D:/rhpwk.xlsx'
    data_set = read_excel_to_set(folder_path)

    # 遍历集合并将内容保存到数据库
    read_set_and_store_in_db(data_set, cursor)

    # 提交更改并关闭连接
    db_connection.commit()
    db_connection.close()
    print("所有数据已成功保存到数据库")